/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;
import controllers.TimeSlot;
import controllers.User;

/**
 *
 * @author Dan
 */
public class TimeSlotDAO extends DAOTest {

    public synchronized static boolean addTimeSlot(TimeSlot tsBean, int driverId) {
        Connection con = getConnection();
        boolean result = false;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_insTimeSlot(?, ?, ?, ?)}");
            cs.setInt(1, driverId); //TODO - change to add logged in user
            cs.setString(2, tsBean.getWeekday());
            cs.setTime(3, tsBean.getStartTime());
            cs.setTime(4, tsBean.getEndTime());
            int added = cs.executeUpdate();

            result = added > 0;

            cs.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public synchronized static boolean addTimeSlot(TimeSlot tsBean) {
        return addTimeSlot(tsBean, tsBean.getDriverID());
    }

    public synchronized static boolean addTimeSlotLogged(TimeSlot tsBean) {
        return addTimeSlot(tsBean, User.getLoggedInUser().getDriverID());
    }

    public static List<TimeSlot> getTimeSlots() {
        Connection con = getConnection();
        ArrayList<TimeSlot> timeSlots = new ArrayList<TimeSlot>();
        try {
            CallableStatement cs = con.prepareCall("{call stp_selAllTimeSlots}");
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("ID");
                int driverID = rs.getInt("DriverID");
                String day = rs.getString("Day");
                Time startTime = rs.getTime("StartTime");
                Time endTime = rs.getTime("EndTime");
                String driverName = rs.getString("DriverName");
                timeSlots.add(new TimeSlot(id, driverID, day, startTime, endTime, driverName));
            }

            cs.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return timeSlots;
    }

    public static List<TimeSlot> getTimeSlotsByDriver(int driverID) {
        Connection con = getConnection();
        ArrayList<TimeSlot> timeSlots = new ArrayList<TimeSlot>();
        try {
            CallableStatement cs = con.prepareCall("{call stp_selTimeSlotsByDriverID(?)}");
            cs.setInt(1, driverID);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("ID");
                String day = rs.getString("Day");
                Time startTime = rs.getTime("StartTime");
                Time endTime = rs.getTime("EndTime");
                String driverName = rs.getString("DriverName");
                timeSlots.add(new TimeSlot(id, driverID, day, startTime, endTime, driverName));
            }

            cs.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return timeSlots;
    }

    public synchronized static boolean deleteTimeSlot(TimeSlot timeSlot) {
        Connection con = getConnection();
        boolean result = false;
        try {
            CallableStatement cs = con.prepareCall("{call stp_delTimeSlot(?)}");
            cs.setInt(1, timeSlot.getId());
            int delete = cs.executeUpdate();

            result = delete > 0;
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
        return result;
    }

    public synchronized static boolean saveTimeSlot(TimeSlot tsBean) {
        Connection con = getConnection();
        boolean result = false;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_updTimeSlot(?, ?, ?, ?)}");
            cs.setInt(1, tsBean.getId());
            cs.setString(2, tsBean.getWeekday());
            cs.setTime(3, tsBean.getStartTime());
            cs.setTime(4, tsBean.getEndTime());
            int added = cs.executeUpdate();

            result = added > 0;

            cs.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }
}
